/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.jgls.bi.dataprocess;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.util.Iterator;
import java.sql.Date;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 *
 * @author Administrator
 */
public class psgprcJdbcDao extends JdbcDaoSupport implements psgprcDao {
    //客单价处理部分函数
    public void psgprcData(Date adTsnDate, int aiShopId, String asShopBm,
            int aiDateNo, String asYear, int aiYybh, int aiMan_id, String asTname) {
        //开始处理
        int TsnAmt = 0;
        int iRc;
        int aiYear;
        int TsnSort;
        int iWeekAmt = 0, iYearAmt = 0, iWeekAmt2 = 0, iYearAmt2 = 0;
        String sql = "";
        float fWeekAddup = 0, fYearAddup = 0, fWeekAddup2 = 0, fYearAddup2 = 0;
        List lstWeek, lstYear, lstPca, lstPsgprcAmt;
        float fPca = 0, fPcaTotal;
        int[] tsn = {0, 0, 0, 0, 0, 0};
        System.out.println("now processing PsgPrc's Data... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        JdbcTemplate jt = getJdbcTemplate();
        sql = "select sum(je)/count(distinct(jqh||empl_id||trnno)) as psgprcvalue  from " + asTname +
                " where saledate = ? and bm=? and trnno<>9999 ";
        Object[] params = new Object[]{adTsnDate, asShopBm};
        int[] types = {Types.DATE, Types.VARCHAR};
        lstPca = jt.query(sql, params, types, new RowMapperResultReader(new PsgPrcMxRM()));
        Iterator Iter = lstPca.iterator();
        PsgPrcMx ltam = new PsgPrcMx();
        while (Iter.hasNext()) {
            ltam = (PsgPrcMx) Iter.next();
            fPca = ltam.getPsgprc_value();
        }

        if (fPca <= 5) {
            TsnSort = 1;
        } else if (fPca > 5 && fPca <= 10) {
            TsnSort = 2;
        } else if (fPca > 10 && fPca <= 15) {
            TsnSort = 3;
        } else if (fPca > 15 && fPca <= 20) {
            TsnSort = 4;
        } else if (fPca > 20 && fPca <= 25) {
            TsnSort = 5;
        } else {
            TsnSort = 6;
        }


        //取周平均累计
        sql = "select week_addup,week_amt from t_bi_psgprc_mx where tsn_date = " +
                "(select max(tsn_date) from t_bi_pca_mx where tsn_date < ? " +
                "and tsn_date_no=? and tsn_id=?)" +
                " and tsn_id = ? and tsn_year = ?";
        params = new Object[]{adTsnDate, aiDateNo, aiShopId, aiShopId,aiYear};
        types = new int[]{Types.DATE, Types.INTEGER, Types.INTEGER, Types.INTEGER,Types.INTEGER};
        lstWeek = jt.query(sql, params, types, new RowMapperResultReader(new PsgPrcMxWeekRM()));
        Iter = lstWeek.iterator();
        ltam = new PsgPrcMx();
        while (Iter.hasNext()) {
            ltam = (PsgPrcMx) Iter.next();
            iWeekAmt2 = ltam.getWeek_amt();
            fWeekAddup2 = ltam.getWeek_addup();
        }

        //取年平均累计
        sql = "select year_addup,year_amt from t_bi_psgprc_mx where tsn_date = (? - 1) and tsn_id = ? and tsn_year = ?";
        params = new Object[]{adTsnDate, aiShopId , aiYear};
        types = new int[]{Types.DATE, Types.INTEGER,Types.INTEGER};
        lstYear = jt.query(sql, params, types, new RowMapperResultReader(new PsgPrcMxYearRM()));
        Iter = lstYear.iterator();
        ltam = new PsgPrcMx();
        while (Iter.hasNext()) {
            ltam = (PsgPrcMx) Iter.next();
            iYearAmt2 = ltam.getYear_amt();
            fYearAddup = ltam.getYear_addup();
        }

        iWeekAmt = iWeekAmt2 + 1;
        fWeekAddup = fWeekAddup2 + fPca;
        iYearAmt = iYearAmt2 + 1;
        fYearAddup = fYearAddup + fPca;

        //begin insert into TsnAmt
        //1:delete
        params = new Object[]{adTsnDate, aiShopId};
        types = new int[]{Types.DATE, Types.INTEGER};
        sql = "delete from t_bi_psgprc_mx where tsn_date = ? and tsn_id = ?";
        iRc = jt.update(sql, params, types);
        //2:insert
        sql = "insert into t_bi_psgprc_mx " +
                "(tsn_date,tsn_id,psgprc_value,tsn_year,tsn_date_no,yybh,man_id,tsn_sort" +
                ",week_addup,week_amt,year_addup,year_amt)" +
                " values(?,?,?,?,?,?,?,?,?,?,?,?)";
        params = new Object[]{adTsnDate, aiShopId, fPca,
            aiYear, aiDateNo, aiYybh, aiMan_id, TsnSort, fWeekAddup, iWeekAmt, fYearAddup, iYearAmt
        };
        types = new int[]{Types.DATE, Types.INTEGER, Types.FLOAT,
            Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.FLOAT, Types.INTEGER, Types.FLOAT, Types.INTEGER
        };
        iRc = jt.update(sql, params, types);
        //插入交易笔数
        sql="select avg(je) as psgprcvalue  from "+asTname+
        "where saledate = ? and bm=? and trnno<>9999 group by jqh,empl_id,trnno ";
        params = new Object[]{adTsnDate, asShopBm};
        types  = new int[] {Types.DATE, Types.VARCHAR};
        lstPsgprcAmt=jt.query(sql,params,types,new RowMapperResultReader(new PsgPrcMxRM()));
        Iter = lstPsgprcAmt.iterator(); 
        PsgPrcMx ltam2 = new PsgPrcMx();
        while(Iter.hasNext()){
            ltam2 = (PsgPrcMx)Iter.next();
            fPca=ltam2.getPsgprc_value();
            
            if (fPca <= 5) {
                TsnSort = 1;
            } else if (fPca > 5 && fPca <= 10) {
                TsnSort = 2;
            } else if (fPca > 10 && fPca <= 15) {
                TsnSort = 3;
            } else if (fPca > 15 && fPca <= 20) {
                TsnSort = 4;
            } else if (fPca > 20 && fPca <= 25) {
                TsnSort = 5;
            } else {
                TsnSort = 6;
            }
            tsn[TsnSort - 1] = tsn[TsnSort - 1] + 1;
        }
        //insert into db
        for(int i=0;i<6;i++){
            sql = "insert into t_bi_psgprc_tsnamt " +
                    "(tsn_date,tsn_id,tsn_amt,yybh,man_id,tsn_sort)" +
                    " values(?,?,?,?,?,?)";
            params = new Object[]{adTsnDate, aiShopId,tsn[i] , 
             aiYybh, aiMan_id, i+1 };
            types = new int[]{Types.DATE, Types.INTEGER, Types.INTEGER, Types.INTEGER, 
            Types.INTEGER, Types.INTEGER};
            iRc = jt.update(sql, params, types);
        }
    
        
    }
    class PsgPrcMxYearRM implements RowMapper {

            public Object mapRow(ResultSet rs, int index)
                    throws SQLException {
                PsgPrcMx wavg = new PsgPrcMx();
                wavg.setYear_addup(new Float(rs.getFloat("year_addup")));
                wavg.setYear_amt(new Integer(rs.getInt("year_amt")));
                return wavg;
            }
        }
    class PsgPrcMxWeekRM implements RowMapper {

            public Object mapRow(ResultSet rs, int index)
                    throws SQLException {
                PsgPrcMx wavg = new PsgPrcMx();
                wavg.setWeek_addup(new Float(rs.getFloat("week_addup")));
                wavg.setWeek_amt(new Integer(rs.getInt("week_amt")));
                return wavg;
            }
        }
    class PsgPrcMxRM implements RowMapper {

            public Object mapRow(ResultSet rs, int index)
                    throws SQLException {
                PsgPrcMx wavg = new PsgPrcMx();
                wavg.setPsgprc_value(new Float(rs.getFloat("psgprcvalue")));
                return wavg;
            }
        }
  
}
